package com.scau.RBAC.dao.impl;

import com.scau.RBAC.dao.UserDao;
import com.scau.RBAC.model.entity.User;
import com.scau.RBAC.util.DBConnectionSingleton;
import com.scau.RBAC.util.DatabaseException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class MySqlUserDao implements UserDao {

    @Override
    public int insert(User entity) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement(
                    "INSERT INTO users(username, password, full_name, email, phone, status, created_time, updated_time, created_by, updated_by) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            ps.setString(1, entity.getUsername());
            ps.setString(2, entity.getPassword());
            ps.setString(3, entity.getFullName());
            ps.setString(4, entity.getEmail());
            ps.setString(5, entity.getPhone());
            ps.setInt(6, entity.getStatus() != null ? entity.getStatus() : 0);
            ps.setObject(7, entity.getCreatedTime());
            ps.setObject(8, entity.getUpdatedTime());
            ps.setLong(9, entity.getCreatedBy() != null ? entity.getCreatedBy() : 0);
            ps.setLong(10, entity.getUpdatedBy() != null ? entity.getUpdatedBy() : 0);

            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("插入用户失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    @Override
    public int batchInsert(List<User> entities) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            conn.setAutoCommit(false);

            ps = conn.prepareStatement(
                    "INSERT INTO users(username, password, full_name, email, phone, status, created_time, updated_time, created_by, updated_by) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            for (User user : entities) {
                ps.setString(1, user.getUsername());
                ps.setString(2, user.getPassword());
                ps.setString(3, user.getFullName());
                ps.setString(4, user.getEmail());
                ps.setString(5, user.getPhone());
                ps.setInt(6, user.getStatus() != null ? user.getStatus() : 0);
                ps.setObject(7, user.getCreatedTime());
                ps.setObject(8, user.getUpdatedTime());
                ps.setLong(9, user.getCreatedBy() != null ? user.getCreatedBy() : 0);
                ps.setLong(10, user.getUpdatedBy() != null ? user.getUpdatedBy() : 0);
                ps.addBatch();
            }

            int[] results = ps.executeBatch();
            conn.commit();

            int total = 0;
            for (int result : results) {
                total += result;
            }
            return total;
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            throw new DatabaseException("批量插入用户失败", e);
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            closeResources(ps, null);
        }
    }

    @Override
    public int deleteById(Long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("DELETE FROM users WHERE id = ?");
            ps.setLong(1, id);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("删除用户失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    @Override
    public int batchDelete(List<Long> ids) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            conn.setAutoCommit(false);

            ps = conn.prepareStatement("DELETE FROM users WHERE id = ?");

            for (Long id : ids) {
                ps.setLong(1, id);
                ps.addBatch();
            }

            int[] results = ps.executeBatch();
            conn.commit();

            int total = 0;
            for (int result : results) {
                total += result;
            }
            return total;
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            throw new DatabaseException("批量删除用户失败", e);
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            closeResources(ps, null);
        }
    }

    @Override
    public int update(User entity) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement(
                    "UPDATE users SET username = ?, password = ?, full_name = ?, email = ?, phone = ?, status = ?, last_login_time = ?, updated_time = ?, updated_by = ? WHERE id = ?");

            ps.setString(1, entity.getUsername());
            ps.setString(2, entity.getPassword());
            ps.setString(3, entity.getFullName());
            ps.setString(4, entity.getEmail());
            ps.setString(5, entity.getPhone());
            ps.setInt(6, entity.getStatus() != null ? entity.getStatus() : 0);
            ps.setObject(7, entity.getLastLoginTime());
            ps.setObject(8, entity.getUpdatedTime());
            ps.setLong(9, entity.getUpdatedBy() != null ? entity.getUpdatedBy() : 0);
            ps.setLong(10, entity.getId());

            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatabaseException("更新用户失败", e);
        } finally {
            closeResources(ps, null);
        }
    }

    @Override
    public User findById(Long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
            ps.setLong(1, id);
            rs = ps.executeQuery();

            if (rs.next()) {
                return mapResultSetToUser(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new DatabaseException("根据ID查询用户失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<User> findAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT * FROM users");
            rs = ps.executeQuery();

            List<User> users = new ArrayList<>();
            while (rs.next()) {
                users.add(mapResultSetToUser(rs));
            }
            return users;
        } catch (SQLException e) {
            throw new DatabaseException("查询所有用户失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<User> findByPage(Map<String, Object> params, int pageNum, int pageSize) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            StringBuilder sql = new StringBuilder("SELECT * FROM users WHERE 1=1");
            List<Object> paramList = new ArrayList<>();

            // 构建查询条件
            buildQueryConditions(sql, params, paramList);

            // 添加分页
            sql.append(" LIMIT ? OFFSET ?");
            paramList.add(pageSize);
            paramList.add((pageNum - 1) * pageSize);

            ps = conn.prepareStatement(sql.toString());

            // 设置参数
            for (int i = 0; i < paramList.size(); i++) {
                ps.setObject(i + 1, paramList.get(i));
            }

            rs = ps.executeQuery();
            List<User> users = new ArrayList<>();
            while (rs.next()) {
                users.add(mapResultSetToUser(rs));
            }
            return users;
        } catch (SQLException e) {
            throw new DatabaseException("分页查询用户失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public long count(Map<String, Object> params) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM users WHERE 1=1");
            List<Object> paramList = new ArrayList<>();

            // 构建查询条件
            buildQueryConditions(sql, params, paramList);

            ps = conn.prepareStatement(sql.toString());

            // 设置参数
            for (int i = 0; i < paramList.size(); i++) {
                ps.setObject(i + 1, paramList.get(i));
            }

            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getLong(1);
            }
            return 0;
        } catch (SQLException e) {
            throw new DatabaseException("统计用户数量失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public User findByUsername(String username) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement("SELECT * FROM users WHERE username = ?");
            ps.setString(1, username);
            rs = ps.executeQuery();

            if (rs.next()) {
                return mapResultSetToUser(rs);
            }
            return null;
        } catch (SQLException e) {
            throw new DatabaseException("根据用户名查询用户失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    @Override
    public List<User> findByRoleId(Long roleId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionSingleton.getInstance().getConnection();
            ps = conn.prepareStatement(
                    "SELECT u.* FROM users u INNER JOIN user_roles ur ON u.id = ur.user_id WHERE ur.role_id = ?");
            ps.setLong(1, roleId);
            rs = ps.executeQuery();

            List<User> users = new ArrayList<>();
            while (rs.next()) {
                users.add(mapResultSetToUser(rs));
            }
            return users;
        } catch (SQLException e) {
            throw new DatabaseException("根据角色ID查询用户失败", e);
        } finally {
            closeResources(ps, rs);
        }
    }

    // 辅助方法：将ResultSet映射到User对象
    private User mapResultSetToUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        user.setFullName(rs.getString("full_name"));
        user.setEmail(rs.getString("email"));
        user.setPhone(rs.getString("phone"));
        user.setStatus(rs.getInt("status"));
        user.setLastLoginTime(rs.getObject("last_login_time", LocalDateTime.class));
        user.setCreatedTime(rs.getObject("created_time", LocalDateTime.class));
        user.setUpdatedTime(rs.getObject("updated_time", LocalDateTime.class));
        user.setCreatedBy(rs.getLong("created_by"));
        user.setUpdatedBy(rs.getLong("updated_by"));
        return user;
    }

    // 辅助方法：构建查询条件
    private void buildQueryConditions(StringBuilder sql, Map<String, Object> params, List<Object> paramList) {
        if (params == null) {
            return;
        }

        if (params.containsKey("username")) {
            sql.append(" AND username LIKE ?");
            paramList.add("%" + params.get("username") + "%");
        }

        if (params.containsKey("status")) {
            sql.append(" AND status = ?");
            paramList.add(params.get("status"));
        }

        if (params.containsKey("startTime") && params.containsKey("endTime")) {
            sql.append(" AND created_time BETWEEN ? AND ?");
            paramList.add(params.get("startTime"));
            paramList.add(params.get("endTime"));
        }
        // 可以根据需要添加更多的查询条件
    }

    // 辅助方法：关闭资源
    private void closeResources(PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}